<?php
function GetGlobalConnectionOptions()
{
	return array(
		'server' => 'localhost',
		'username' => 'tom',
		'password' => 'hines',
		'database' => 'IPT2012tomandjack'
		);
};

//Connect
$condet = GetGlobalConnectionOptions();
$con = mysql_connect($condet["server"],$condet["username"],$condet["password"]);
if (!$con)
{
	die("Could not connect: " . mysql_error());
}

//Select DB
mysql_select_db($condet["database"], $con);

//Code
//Create and record reorders that need to be made
$qry = "INSERT INTO tblreorders
SELECT NULL AS reorder, supplier, NOW() AS creationdate
FROM (
SELECT DISTINCT supplier
FROM tblitems
WHERE targetstock-stock > 0
ORDER BY supplier) tbl1;";
mysql_query($qry);

$qry = "SELECT DISTINCT reorder
FROM (
SELECT tblreorders.reorder, tblreorderitems.item
FROM tblreorderitems RIGHT JOIN tblreorders
ON tblreorderitems.reorder = tblreorders.reorder) tbl1
WHERE item IS NULL;";
$reorderids = mysql_query($qry);

$qry = "INSERT INTO tblreorderitems
SELECT tbl2.reorder, tblitems.item, tblitems.targetstock-tblitems.stock AS quantity
FROM tblitems RIGHT JOIN (
SELECT DISTINCT reorder, supplier
FROM (
SELECT tblreorders.reorder, tblreorders.supplier, tblreorderitems.item
FROM tblreorderitems RIGHT JOIN tblreorders
ON tblreorderitems.reorder = tblreorders.reorder) tbl1
WHERE item IS NULL) tbl2
ON tblitems.supplier = tbl2.supplier
WHERE tblitems.targetstock-tblitems.stock > 0;";
mysql_query($qry);

//Set values for mail
$subject = "Order Details";
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: SoftPlus Pty Ltd <softplusptyltd@gmail.com>' . "\r\n";

//Get the details of the reorders and send them to the suppliers
while($reorderid = mysql_fetch_array($reorderids))
{
	//Get details of this reorder
	$qry = "SELECT tblreorders.reorder, tblreorders.creationdate, tblsuppliers.name, tblsuppliers.email
		FROM tblreorders, tblsuppliers
		WHERE tblreorders.reorder = '".$reorderid["reorder"]."'
		AND tblsuppliers.supplier = tblreorders.supplier;";
	$reorderdet = mysql_fetch_array(mysql_query($qry));
	//Get details of the items in this reorder
	$qry = "SELECT tblreorderitems.item, tblreorderitems.quantity, tblitems.name, tblitems.wholesaleprice, tblreorderitems.quantity*tblitems.wholesaleprice AS total
		FROM tblreorderitems, tblitems
		WHERE tblreorderitems.item = tblitems.item
		AND tblreorderitems.reorder = '".$reorderid["reorder"]."'
		ORDER BY tblitems.name;";
	$reorderitems = mysql_query($qry);
	//Get total money value of this reorder
	$qry = "SELECT SUM(tblreorderitems.quantity*tblitems.wholesaleprice) AS total
		FROM tblreorderitems, tblitems
		WHERE tblreorderitems.item = tblitems.item
		AND tblreorderitems.reorder = '".$reorderid["reorder"]."';";
	$reordertotal = mysql_fetch_array(mysql_query($qry));
	//The target email would use the following line if the system was to be implemented
	//$to = $reorderdet["email"];
	$to = "hineta64@gmail.com";
	$message = "
	<html>
	<head>
		<title>SoftPlus Pty Ltd</title>
	</head>
	<body>
	";
	//Title
	$message .= "<p><b>SoftPlus Pty Ltd</b></p></br>";
	//Our details
	$message .= "WELLINGON POINT, QLD, 4160</br>
		     Building 27, 38 Baar St</br>
		     Phone: 041 763 32 32</br>
		     Email: softplus@gmail.com</br></br>";
	//Reorder details
	$message .= "Order #";
	$message .= $reorderdet["reorder"];
	$message .= ", Date: ";
	$message .= $reorderdet["creationdate"];
	$message .= "</br></br>";
	//To (supplier details)
	$message .= "To:</br>";
	$message .= $reorderdet["name"];
	$message .= "</br>";
	$message .= $reorderdet["email"];
	$message .= "</br></br>";
	//Order (item list etc)
	$message .= "<table width='840px' border='1' cellpadding='0' cellspacing='0'>
	<tr>
	<th><b>Barcode</b></th>
	<th><b>Name</b></th>
	<th><b>Price per unit</b></th>
	<th><b>Quantity</b></th>
	<th><b>Total</b></th>
	</tr>";
	while($item = mysql_fetch_array($reorderitems))
	{
		$message .= "<tr>";
		$message .= "<td align='middle'>".$item["item"]."</td>";
		$message .= "<td align='middle'>".$item["name"]."</td>";
		$message .= "<td align='right'>$".$item["wholesaleprice"]."</td>";
		$message .= "<td align='middle'>".$item["quantity"]."</td>";
		$message .= "<td align='right'>$".$item["total"]."</td>";
		$message .= "</tr>";
	};
	//Total
	$message .= "<tr><td></td><td></td><td></td><td align='middle'><b>Total</b></td><td align='right'>$".$reordertotal["total"]."</td></tr>";
	$message .= "</table></body></html>";
	mail($to, $subject, $message, $headers);
	//Following line is for testing purposes
	//echo $message;
};

//Disconnect
mysql_close($con);

//Redirect back home
header("Location: http://iptsites.justbunto.com/tom/");
?>
